{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'zhangsan', 'male', 18), (2, 'lisi', 'female', 19), (3, 'wangwu', 'male', 17)]\n"
     ]
    }
   ],
   "source": [
    "db = sqlite3.connect(\"university.db\")\n",
    "cursor=db.cursor()\n",
    "cursor.execute(\"\"\"CREATE TABLE IF NOT EXISTS students\n",
    "(id integer PRIMARY KEY, name text NOT NULL, gender text NOT NULL, age ineger NOT NULL);\"\"\")\n",
    "cursor.execute(\"\"\"INSERT INTO students(id, name, gender, age) \n",
    "VALUES(1, 'zhangsan', 'male', 18)\"\"\")\n",
    "cursor.execute(\"\"\"INSERT INTO students(id, name, gender, age) \n",
    "VALUES(2, 'lisi', 'female', 19)\"\"\")\n",
    "cursor.execute(\"\"\"INSERT INTO students(id, name, gender, age) \n",
    "VALUES(3, 'wangwu', 'male', 17)\"\"\")\n",
    "db.commit()\n",
    "cursor.execute(\"SELECT * FROM students\")\n",
    "print(cursor.fetchall())\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(100, 'Python', 'CZ'), (101, 'Algorithm', 'GM'), (102, 'SQL', 'WX')]\n"
     ]
    }
   ],
   "source": [
    "db = sqlite3.connect(\"university.db\")\n",
    "cursor=db.cursor()\n",
    "cursor.execute(\"\"\"CREATE TABLE IF NOT EXISTS class\n",
    "(id integer PRIMARY KEY, name text NOT NULL, teacher text NOT NULL);\"\"\")\n",
    "cursor.execute(\"\"\"INSERT INTO class(id, name, teacher) \n",
    "VALUES(100, 'Python', 'CZ')\"\"\")\n",
    "cursor.execute(\"\"\"INSERT INTO class(id, name, teacher) \n",
    "VALUES(101, 'Algorithm', 'GM')\"\"\")\n",
    "cursor.execute(\"\"\"INSERT INTO class(id, name, teacher) \n",
    "VALUES(102, 'SQL', 'WX')\"\"\")\n",
    "db.commit()\n",
    "cursor.execute(\"SELECT * FROM class\")\n",
    "print(cursor.fetchall())\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 100, 80), (2, 102, 90), (3, 101, 70)]\n"
     ]
    }
   ],
   "source": [
    "db = sqlite3.connect(\"university.db\")\n",
    "cursor=db.cursor()\n",
    "cursor.execute(\"\"\"CREATE TABLE IF NOT EXISTS enrolled\n",
    "(student integer NOT NULL, class integer NOT NULL, grade integer NOT NULL);\"\"\")\n",
    "cursor.execute(\"\"\"INSERT INTO enrolled(student, class, grade) \n",
    "VALUES(1, 100, 80)\"\"\")\n",
    "cursor.execute(\"\"\"INSERT INTO enrolled(student, class, grade) \n",
    "VALUES(2, 102, 90)\"\"\")\n",
    "cursor.execute(\"\"\"INSERT INTO enrolled(student, class, grade) \n",
    "VALUES(3, 101, 70)\"\"\")\n",
    "db.commit()\n",
    "cursor.execute(\"SELECT * FROM enrolled\")\n",
    "print(cursor.fetchall())\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'zhangsan', 'male', 18, 100, 80), (2, 'lisi', 'female', 19, 102, 90), (3, 'wangwu', 'male', 17, 101, 70)]\n"
     ]
    }
   ],
   "source": [
    "db = sqlite3.connect(\"university.db\")\n",
    "cursor=db.cursor()\n",
    "cursor.execute(\"\"\"SELECT students.id, students.name, students.gender, students.age, enrolled.class, enrolled.grade FROM students JOIN enrolled on students.id = enrolled.student\"\"\")\n",
    "print(cursor.fetchall())\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
